package com.budwk.app.sqfwV2.service.impl;

import cn.hutool.core.date.DateUtil;
import cn.hutool.json.JSONUtil;
import com.alibaba.fastjson.JSONObject;
import com.budwk.app.common.config.AppException;
import com.budwk.app.exception.ServiceException;
import com.budwk.app.sqfw.constants.CommonConstants;
import com.budwk.app.sqfw.constants.SqfwFormSummaryConstants;
import com.budwk.app.sqfw.dto.PeopleTagDTO;
import com.budwk.app.sqfw.enums.SqfwFormSummaryFieldEnum;
import com.budwk.app.sqfw.enums.StreetEnum;
import com.budwk.app.sqfw.enums.SupportBusinessTypeEnum;
import com.budwk.app.sqfw.models.Sqfw_formSummary_new;
import com.budwk.app.sqfw.services.SqfwFormsummaryNewService;
import com.budwk.app.sqfwV2.component.ShopsComponent;
import com.budwk.app.sqfwV2.dto.PlaceParamDTO;
import com.budwk.app.sqfwV2.dto.dto.SqfwShopBaseInfoDTO;
import com.budwk.app.sqfwV2.dto.entity.SqfwShopsBaseInfo;
import com.budwk.app.sqfwV2.dto.vo.JurisdictionShopQueryVo;
import com.budwk.app.sqfwV2.enums.CheckTimeoutEnum;
import com.budwk.app.sqfwV2.service.ShopsBaseInfoService;
import com.budwk.app.sys.models.Sys_dict;
import com.budwk.app.sys.models.Sys_unit;
import com.budwk.app.sys.models.Sys_user;
import com.budwk.app.sys.services.SysDictService;
import com.budwk.app.sys.services.SysUnitService;
import com.budwk.app.sys.services.SysUserService;
import com.budwk.app.util.PoiTlUtil;
import com.budwk.app.util.StrUtil;
import com.budwk.app.util.poiUtils.CommonFormOfficeUtils;
import com.budwk.app.util.poiUtils.excelPictureConfigBO.ExcelFileCreateParamBO;
import com.budwk.starter.common.page.PageUtil;
import com.budwk.starter.common.page.Pagination;
import com.budwk.starter.common.result.Result;
import com.budwk.starter.common.result.ResultCode;
import com.budwk.starter.database.service.BaseServiceImpl;
import com.budwk.starter.security.utils.SecurityUtil;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.compress.utils.Lists;
import org.apache.commons.lang3.ObjectUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Sheet;
import org.nutz.dao.Cnd;
import org.nutz.dao.Dao;
import org.nutz.dao.Sqls;
import org.nutz.dao.util.cri.SqlExpressionGroup;
import org.nutz.ioc.loader.annotation.Inject;
import org.nutz.ioc.loader.annotation.IocBean;
import org.nutz.lang.Strings;
import org.nutz.lang.util.NutMap;
import org.nutz.mvc.upload.TempFile;

import java.time.LocalDateTime;
import java.time.ZoneId;
import java.util.*;
import java.util.stream.Collectors;

import static com.budwk.app.util.poiUtils.CommonFormOfficeUtils.getTemplatePathByOS;

@Slf4j
@IocBean(args = {"refer:dao"})
public class ShopsBaseInfoServiceImpl extends BaseServiceImpl<SqfwShopsBaseInfo> implements ShopsBaseInfoService {
    public ShopsBaseInfoServiceImpl(Dao dao) {
        super(dao);
    }

    @Inject
    private SysUserService userService;

    @Inject
    private SqfwFormsummaryNewService formsummaryNewService;

    @Inject
    private SysUnitService unitService;
    @Inject
    private ShopsComponent shopsComponent;

    @Inject
    private SysDictService dictService;

    private String getServiceTypeStr(SqfwShopsBaseInfo shopsBaseInfo, String serviceType) {
        if (StringUtils.isEmpty(shopsBaseInfo.getServiceType())) {
            return serviceType;
        }
        if (StringUtils.isEmpty(serviceType)) {
            return shopsBaseInfo.getServiceType();
        }
        if (!serviceType.contains(shopsBaseInfo.getServiceType())) {
            return String.format("%s%s%s", serviceType, ",", shopsBaseInfo.getServiceType());
        }
        return serviceType;
    }

    /**
     * 按照五级+商品名称+地址新增或修改
     */
    @Override
    public SqfwShopsBaseInfo insertOrUpdateData(SqfwShopsBaseInfo shopsBaseInfo, Sqfw_formSummary_new formSummary) {
        String shopsBaseInfoId = formSummary.getShopsBaseInfoId();
        // 如果是修改
        if (StringUtils.isNotEmpty(shopsBaseInfoId)) {
            SqfwShopsBaseInfo s = this.fetch(shopsBaseInfoId);
            shopsBaseInfo.setProvince(s.getProvince())
                    .setCity(s.getCity())
                    .setCounty(s.getCounty())
                    .setDistrict(s.getDistrict())
                    .setCommunity(s.getCommunity())
                    .setPlaceName(StringUtils.isNotEmpty(shopsBaseInfo.getPlaceName()) ? shopsBaseInfo.getPlaceName() : s.getPlaceName())
                    .setAddress(StringUtils.isNotEmpty(shopsBaseInfo.getAddress()) ? shopsBaseInfo.getAddress() : s.getAddress())
                    .setPlaceArea(StringUtils.isNotEmpty(shopsBaseInfo.getPlaceArea()) ? shopsBaseInfo.getPlaceArea() : s.getPlaceArea());
        } else {
            shopsBaseInfo.setProvince(formSummary.getProvince())
                    .setCity(formSummary.getCity())
                    .setCounty(formSummary.getCounty())
                    .setCommunity(formSummary.getCommunity())
                    .setDistrict(formSummary.getDistrict());
        }
        // 1 校验参数
        checkAddParam(shopsBaseInfo);
        // 2 构造查询条件
        ShopsComponent.buildDetailAddress(shopsBaseInfo);
        SqfwShopsBaseInfo s = this.findByDetailAddress(shopsBaseInfo.getDetailAddress());
        if (s != null) {
            s.setUpdateDate(LocalDateTime.now())
                    .setUpdateId(SecurityUtil.getUserId())
                    .setType(formSummary.getType())
                    .setDeleted(SqfwFormSummaryFieldEnum.CHECK_STATUS_CLOSED.getCode().equals(formSummary.getInspectionSituation()))
                    .setShopStatus(SqfwFormSummaryFieldEnum.check_status_checked.getCode())
                    .setInspectionSituation(StringUtils.isNotEmpty(shopsBaseInfo.getInspectionSituation()) ? shopsBaseInfo.getInspectionSituation() : formSummary.getInspectionSituation())
                    .setOperator(shopsBaseInfo.getOperator())
                    .setOperatorTelephone(shopsBaseInfo.getOperatorTelephone())
                    .setPlaceArea(shopsBaseInfo.getPlaceArea())
                    .setMainBusinessScope(shopsBaseInfo.getMainBusinessScope());
            if (formSummary.getFillDate() != null) {
                s.setFillDate(formSummary.getFillDate().toInstant().atZone(ZoneId.systemDefault()).toLocalDateTime());
            }
            s.setServiceType(getServiceTypeStr(shopsBaseInfo, s.getServiceType()));
            this.updateIgnoreNull(s);
            return s;
        }
        // 不存在 则新增
        shopsBaseInfo.setCreateDate(LocalDateTime.now())
                .setCreateId(SecurityUtil.getUserId())
                .setUpdateDate(LocalDateTime.now())
                .setUpdateId(SecurityUtil.getUserId())
                .setDeleted(false)
                .setInspectionSituation(StringUtils.isNotEmpty(shopsBaseInfo.getInspectionSituation()) ? shopsBaseInfo.getInspectionSituation() : formSummary.getInspectionSituation())
                .setShopStatus(StringUtils.isNotEmpty(shopsBaseInfo.getShopStatus()) ? shopsBaseInfo.getShopStatus() : formSummary.getCheckStatus())
                .setType(formSummary.getType());
        if (formSummary.getFillDate() != null) {
            shopsBaseInfo.setFillDate(formSummary.getFillDate().toInstant().atZone(ZoneId.systemDefault()).toLocalDateTime());
        }
        this.insert(shopsBaseInfo);
        return shopsBaseInfo;
    }

    private void checkAddParam(SqfwShopsBaseInfo shopsBaseInfo) {
        if (StringUtils.isEmpty(shopsBaseInfo.getPlaceName())) {
            throw new AppException("商铺名称不能为空！");
        }
        if (StringUtils.isEmpty(shopsBaseInfo.getAddress())) {
            throw new AppException("商铺地址不能为空！");
        }
        if (StringUtils.isEmpty(shopsBaseInfo.getProvince())) {
            throw new AppException("省级不能为空！");
        }
        if (StringUtils.isEmpty(shopsBaseInfo.getCity())) {
            throw new AppException("市级不能为空！");
        }
        if (StringUtils.isEmpty(shopsBaseInfo.getDistrict())) {
            throw new AppException("区县不能为空！");
        }
        if (StringUtils.isEmpty(shopsBaseInfo.getCounty())) {
            throw new AppException("街道不能为空！");
        }
        if (StringUtils.isEmpty(shopsBaseInfo.getCommunity())) {
            throw new AppException("社区不能为空！");
        }
        shopsBaseInfo.setPlaceName(shopsBaseInfo.getPlaceName().trim());
        shopsBaseInfo.setAddress(shopsBaseInfo.getAddress().trim());
    }

    @Override
    public List<SqfwShopsBaseInfo> findByPlaceName(String placeName) {
        Cnd cnd = Cnd.NEW();
        SqlExpressionGroup sqlGroup = cnd.where();
        if (StringUtils.isNotEmpty(placeName)) {
            sqlGroup.andLike(SqfwShopsBaseInfo::getPlaceName, placeName);
        }
        sqlGroup.andEquals(SqfwShopsBaseInfo::getDeleted, false)
                .andNotEquals(SqfwShopsBaseInfo::getShopStatus, SqfwFormSummaryFieldEnum.CHECK_STATUS_CLOSED.getCode());
        cnd.getOrderBy().desc(SqfwShopsBaseInfo::getCreateDate);
        List<SqfwShopsBaseInfo> list = query(cnd);
        return list;
    }

    @Override
    public List<SqfwShopsBaseInfo> listByCondition(String queryKey, String queryContent) {
        Cnd cnd = Cnd.NEW();
        SqlExpressionGroup sqlGroup = cnd.where();
        if (StringUtils.isNotEmpty(queryKey)) {
            sqlGroup.andLike(queryKey, queryContent);
        }
        // 按照五级查询
        Sys_user user = userService.fetch(SecurityUtil.getUserId());
        sqlGroup.andEquals(SqfwShopsBaseInfo::getDeleted, false)
                .andEquals(SqfwShopsBaseInfo::getProvince, user.getProvince())
                .andEquals(SqfwShopsBaseInfo::getCity, user.getCity())
                .andEquals(SqfwShopsBaseInfo::getDistrict, user.getDistrict())
                .andEquals(SqfwShopsBaseInfo::getCounty, user.getCounty())
                .andNotEquals(SqfwShopsBaseInfo::getShopStatus, SqfwFormSummaryFieldEnum.CHECK_STATUS_CLOSED.getCode());
        // 社区不是直属 只能查看他自己的数据
        if (!CommonConstants.DIRECTLY_UNDER.equals(user.getCommunity())) {
            sqlGroup.andEquals(SqfwShopsBaseInfo::getCommunity, user.getCommunity());
        }
        cnd.getOrderBy().desc(SqfwShopsBaseInfo::getCreateDate);
        cnd.limit(10); // 默认查10条数据
        List<SqfwShopsBaseInfo> list = query(cnd);
        // 如果详细地址为空 更新详细地址
        if (list.size() == 0) {
            return list;
        }
        shopsComponent.initUpdateAddress(list);
        return list;
    }


    @Override
    public List<SqfwShopsBaseInfo> getShopInfoByPlaceName(String content, Integer pageSize, Integer pageNo, String type) {
        Sys_user sysUser = userService.getUserById(SecurityUtil.getUserId());
        Cnd cnd = Cnd.NEW();
        if (StringUtils.isNotBlank(content)) {
            cnd.and("place_name", "like", "%" + content + "%");
        }
        cnd.and("province", "=", sysUser.getProvince());
        cnd.and("city", "=", sysUser.getCity());
        cnd.and("district", "=", sysUser.getDistrict());
        cnd.and("county", "=", sysUser.getCounty());
        if (!CommonConstants.DIRECTLY_UNDER.equals(sysUser.getCommunity())) {
            cnd.and("community", "=", sysUser.getCommunity());
        }
        if (ObjectUtils.isNotEmpty(type)) {
            cnd.and("service_type", "like", "%" + type + "%");
        }
        //cnd.and("deleted", "!=", true);
        //cnd.and("shop_status", "!=", SqfwFormSummaryFieldEnum.CHECK_STATUS_CLOSED.getCode());
        cnd.and(Cnd.exps("shop_status", "!=", SqfwFormSummaryFieldEnum.CHECK_STATUS_CLOSED.getCode()).orIsNull("shop_status"));
        cnd.orderBy("update_date", "desc");
        //cnd.limit(20);
        if (null != pageNo) {
            cnd.limit(pageNo, pageSize);
        }
        List<SqfwShopsBaseInfo> list = query(cnd);
        if (list.size() == 0) {
            return list;
        }
        // 检查详细地址是否为空 处理空数据
        shopsComponent.initUpdateAddress(list);
        return list;
    }

    @Override
    public Result<?> getShopInfoList(SqfwShopBaseInfoDTO bean, int pageNo, int pageSize, String pageOrderName, String pageOrderBy) {
        Cnd cnd = Cnd.NEW();
        if (Strings.isNotBlank(pageOrderName) && Strings.isNotBlank(pageOrderBy)) {
            cnd.orderBy(pageOrderName, PageUtil.getOrder(pageOrderBy));
        } else {
            cnd.orderBy("update_date", "desc");
        }
        List<String> userIds = Lists.newArrayList();
        if (Strings.isNotBlank(bean.getCreateId())) {
            Cnd cnd1 = Cnd.NEW();
            cnd1.and("username", "like", "%" + bean.getCreateId() + "%");
            List<Sys_user> sysUsers = userService.query(cnd1);
            userIds = sysUsers.stream().map(Sys_user::getId).distinct().collect(Collectors.toList());
        }
        if (!userIds.isEmpty()) {
            cnd.and("create_id", "in", userIds);
        }
        Sys_user user = userService.getUserById(SecurityUtil.getUserId());
        if (!CommonConstants.DIRECTLY_UNDER.equals(user.getProvince())) {
            cnd.and("province", "=", user.getProvince());
        }
        if (!CommonConstants.DIRECTLY_UNDER.equals(user.getCity())) {
            cnd.and("city", "=", user.getCity());
        }
        if (!CommonConstants.DIRECTLY_UNDER.equals(user.getDistrict())) {
            cnd.and("district", "=", user.getDistrict());
        }
        if (!CommonConstants.DIRECTLY_UNDER.equals(user.getCounty())) {
            cnd.and("county", "=", user.getCounty());
        }
        if (!CommonConstants.DIRECTLY_UNDER.equals(user.getCommunity())) {
            cnd.and("community", "=", user.getCommunity());
        }

        if (Strings.isNotBlank(bean.getProvince())) {
            cnd.and("province", "like", "%" + bean.getProvince() + "%");
        }
        if (Strings.isNotBlank(bean.getCity())) {
            cnd.and("city", "like", "%" + bean.getCity() + "%");
        }
        if (Strings.isNotBlank(bean.getCounty())) {
            cnd.and("county", "like", "%" + bean.getCounty() + "%");
        }
        if (Strings.isNotBlank(bean.getDistrict())) {
            cnd.and("district", "like", "%" + bean.getDistrict() + "%");
        }
        if (Strings.isNotBlank(bean.getCommunity())) {
            cnd.and("community", "like", "%" + bean.getCommunity() + "%");
        }
        if (Strings.isNotBlank(bean.getOperator())) {
            cnd.and("operator", "like", "%" + bean.getOperator() + "%");
        }
        if (Strings.isNotBlank(bean.getOperatorTelephone())) {
            cnd.and("operator_telephone", "like", "%" + bean.getOperatorTelephone() + "%");
        }
        if (Strings.isNotBlank(bean.getType())) {
            cnd.and("type", "like", "%" + bean.getType() + "%");
        }
        if (Strings.isNotBlank(bean.getPlaceName())) {
            cnd.and("place_name", "like", "%" + bean.getPlaceName() + "%");
        }
        if (Strings.isNotBlank(bean.getAddress())) {
            cnd.and("address", "like", "%" + bean.getAddress() + "%");
        }
        if (Strings.isNotBlank(bean.getBusinessLicenseName())) {
            cnd.and("business_license_name", "like", "%" + bean.getBusinessLicenseName() + "%");
        }
        if (Strings.isNotBlank(bean.getMainBusinessScope())) {
            cnd.and("main_business_scope", "like", "%" + bean.getMainBusinessScope() + "%");
        }
        if (Strings.isNotBlank(bean.getSocialCreditCode())) {
            cnd.and("social_credit_code", "like", "%" + bean.getSocialCreditCode() + "%");
        }
        if (Strings.isNotBlank(bean.getPlaceArea())) {
            cnd.and("place_area", "like", "%" + bean.getPlaceArea() + "%");
        }
        if (Strings.isNotBlank(bean.getBusinessStatus())) {
            cnd.and("deleted", "=", bean.getBusinessStatus());
        }

        //数据库中存在这种"0001-01-01 00:00:00.000" 历史数据， 需要过滤掉
        //cnd.and("fill_date", "=", DateUtil.parse("0001-01-01 00:00:00.000"));

        if (bean.getQueryTime() != null && Strings.isNotBlank(bean.getQueryTime())) {
            String time[] = bean.getQueryTime().split(",");
            cnd.and("update_date", "between", new Object[]{new Date(Long.valueOf(time[0])), new Date(Long.valueOf(time[1]))});
        }
        Pagination pagination = listPage(pageNo, pageSize, cnd);
        List<SqfwShopsBaseInfo> list = pagination.getList(SqfwShopsBaseInfo.class);
        shopsComponent.initUpdateAddress(list);
        List<SqfwShopsBaseInfo> resList = Lists.newArrayList();
        Map mapUser = userService.getMap(Sqls.create("select id,username from shequ.sys_user"));
        for (SqfwShopsBaseInfo info : list) {
            //Sys_user sysUser = userService.fetch(info.getCreateId());
            Object name = mapUser.get(info.getCreateId());
            if (!ObjectUtils.isEmpty(name)) {
                info.setCreateId(String.valueOf(name));
            }
            resList.add(info);
        }
        pagination.setList(resList);
        pagination.setPageNo(pageNo);
        pagination.setPageSize(pageSize);
        return Result.success(pagination);
    }

    @Override
    public Result<?> importShopInfoData(TempFile file, String communityId) {
        if (StringUtils.isBlank(communityId)) {
            throw new ServiceException("请选择需导入的社区！");
        }
        try {
            // 1 获取表格的单元格
            Sheet sheet = PoiTlUtil.getBookSheet(file);
            // 2 获取导入的数据
            //List<SqfwShopsBaseInfo> importDatas = getShopInfoImportData(sheet);
            // 获取所有导入的数据
            List<JSONObject> importContents = PoiTlUtil.getImportContent(sheet, 2);
            if (importContents.isEmpty()) {
                return Result.error("无数据！");
            }
            List<String> contentList = Lists.newArrayList();
            List<String> placeNameList = Lists.newArrayList();
            List<SqfwShopsBaseInfo> results = new ArrayList<>();
            int num = 2;
            Sys_user user = userService.getUserById(SecurityUtil.getUserId());
            Sys_unit unit = unitService.fetch(communityId);
            for (JSONObject json : importContents) {
                num++;
                StringBuilder str = new StringBuilder();
                str.append("第").append(num).append("行").append(":");
                SqfwShopsBaseInfo shopsBaseInfo = JSONObject.toJavaObject(json, SqfwShopsBaseInfo.class);
                shopsBaseInfo.setDeleted(false);
                shopsBaseInfo.setCreateDate(DateUtil.parse(CommonConstants.WASTE_CREATE_TIME).toLocalDateTime());
                shopsBaseInfo.setUpdateDate(LocalDateTime.now());
                shopsBaseInfo.setCreateId(user.getId());
                shopsBaseInfo.setUpdateId(user.getId());
                shopsBaseInfo.setProvince(unit.getProvince());
                shopsBaseInfo.setCity(unit.getCity());
                shopsBaseInfo.setDistrict(unit.getDistrict());
                shopsBaseInfo.setCounty(unit.getCounty());
                shopsBaseInfo.setCommunity(unit.getCommunity());
                if (StringUtils.isEmpty(shopsBaseInfo.getShopStatus())) {
                    shopsBaseInfo.setShopStatus(SqfwFormSummaryFieldEnum.check_status_init.getCode());
                }
                if (StringUtils.isEmpty(shopsBaseInfo.getInspectionSituation())) {
                    shopsBaseInfo.setInspectionSituation(SqfwFormSummaryFieldEnum.CHECK_SITUATION_NO.getCode());
                }
                // 处理空字符
                StrUtil.handleStrTrim(shopsBaseInfo);
                shopsBaseInfo.setCreateId(SecurityUtil.getUserId());
                if (StringUtils.isBlank(shopsBaseInfo.getPlaceName())) {
                    str.append("【placeName】字段不能为空！");
                    contentList.add(str.toString());
                } else if (placeNameList.contains(shopsBaseInfo.getPlaceName())) {
                    str.append("【").append(shopsBaseInfo.getPlaceName()).append("】").append("商铺名称重复！");
                    contentList.add(str.toString());
                } else {
                    if (unit.getCounty().contains("世纪城")) {
                        if (StringUtils.isBlank(shopsBaseInfo.getType())) {
                            str.append("【type】字段不能为空！");
                            contentList.add(str.toString());
                        }
                    }
                    results.add(shopsBaseInfo);
                }
                placeNameList.add(shopsBaseInfo.getPlaceName());
            }
            if (!contentList.isEmpty()) {
                return Result.data(ResultCode.FAILURE.getCode(), contentList, "导入的数据有误");
            }
            // 3 新增数据
            this.fastInsert(results);
        } catch (Exception e) {
            log.error("ShopsBaseInfoServiceImpl.importShopInfoData导入商铺数据失败:" + e.getMessage());
            throw new ServiceException("ShopsBaseInfoServiceImpl.importShopInfoData导入商铺数据失败:" + e.getMessage());
        }
        return Result.success();
    }

    @Override
    public Result<?> queryCommunity(String communityName) {
        Sys_user sysUser = userService.getUserById(SecurityUtil.getUserId());
        StringBuilder sql = new StringBuilder();
        sql.append(" select id,concat(province ,city ,district ,county,community) as name from (");
        sql.append(" select *, row_number() over(partition by province ,city ,district ,county,community order by updatedat  desc) as num  from shequ.sys_unit ");
        sql.append(" ) a where a.num=1 ");
        sql.append(" and province is not null and city is not null and district is not null and county is not null and community is not null ");
        if (!CommonConstants.DIRECTLY_UNDER.equals(sysUser.getProvince())) {
            sql.append(" and province ='").append(sysUser.getProvince()).append("'");
        }
        if (!CommonConstants.DIRECTLY_UNDER.equals(sysUser.getCity())) {
            sql.append(" and city ='").append(sysUser.getCity()).append("'");
        }
        if (!CommonConstants.DIRECTLY_UNDER.equals(sysUser.getDistrict())) {
            sql.append(" and district ='").append(sysUser.getDistrict()).append("'");
        }
        if (!CommonConstants.DIRECTLY_UNDER.equals(sysUser.getCounty())) {
            sql.append(" and county ='").append(sysUser.getCounty()).append("'");
        }
        if (!CommonConstants.DIRECTLY_UNDER.equals(sysUser.getCommunity())) {
            sql.append(" and community ='").append(sysUser.getCommunity()).append("'");
        }
        if (Strings.isNotBlank(communityName)) {
            sql.append(" and ( community like '%").append(communityName).append("%'").append(" or county like '%").append(communityName).append("%')");
        } else {
            throw new ServiceException("请输入参数！");
        }
        sql.append(" order by updatedat desc ");
        return Result.data(listMap(Sqls.create(sql.toString())));
    }

    @Override
    public String downloadTemplate() {
        ExcelFileCreateParamBO efcBO = new ExcelFileCreateParamBO();
        String templatePath = getTemplatePathByOS("/shopInfo.xlsx");
        efcBO.setTemplatePath(templatePath);
        efcBO.setFileName("商铺信息模板");
        efcBO.setDataList(Lists.newArrayList());
        return CommonFormOfficeUtils.createExcelAndUpload(efcBO);
    }

    @Override
    public void deletedShopInfo(Sqfw_formSummary_new item) {
        if (StringUtils.isNotBlank(item.getShopsBaseInfoId())) {
            Cnd cnd = Cnd.where("id", "=", item.getShopsBaseInfoId()).and("create_date", ">", "2000-01-01");
            SqfwShopsBaseInfo shopsBaseInfo = this.fetch(cnd);
            if (!ObjectUtils.isEmpty(shopsBaseInfo)) {
                int count = formsummaryNewService.count(Cnd.where(Sqfw_formSummary_new::getShopsBaseInfoId, "=", shopsBaseInfo.getId()));
                if (count == 1) {
                    this.delete(shopsBaseInfo.getId());
                }
            }
        }
    }

    @Override
    public Result<Pagination> listJurisdictionShop(JurisdictionShopQueryVo baseInfo) {
        Cnd cnd = Cnd.NEW();
        SqlExpressionGroup sqlGroup = cnd.where();
        if ("1".equals(baseInfo.getStatus())) {
            sqlGroup.andEquals(SqfwShopsBaseInfo::getInspectionSituation, SqfwFormSummaryFieldEnum.CHECK_SITUATION_YES.getCode());
        }
        if ("2".equals(baseInfo.getStatus())) {
            sqlGroup.andEquals(SqfwShopsBaseInfo::getInspectionSituation, SqfwFormSummaryFieldEnum.CHECK_SITUATION_NO.getCode());
        }
        if ("3".equals(baseInfo.getStatus())) {
            sqlGroup.andEquals(SqfwShopsBaseInfo::getShopStatus, SqfwFormSummaryFieldEnum.CHECK_STATUS_CLOSED.getCode());
        }
        if (StringUtils.isNotEmpty(baseInfo.getContent())) {
            SqlExpressionGroup pSql = Cnd.exps(SqfwShopsBaseInfo::getPlaceName, "like", "%" + baseInfo.getContent() + "%");
            SqlExpressionGroup aSql = Cnd.exps(SqfwShopsBaseInfo::getAddress, "like", "%" + baseInfo.getContent() + "%");
            sqlGroup.and(pSql.or(aSql));
        }
        cnd.getOrderBy().desc(SqfwShopsBaseInfo::getFillDate);
        if (StringUtils.isNotEmpty(baseInfo.getServiceType())) {
            sqlGroup.andLike(SqfwShopsBaseInfo::getServiceType, baseInfo.getServiceType());
        }
        // 五级查询
        Sys_user sysUser = userService.getUserById(SecurityUtil.getUserId());
        sqlGroup.andEquals(SqfwShopsBaseInfo::getProvince, sysUser.getProvince())
                .andEquals(SqfwShopsBaseInfo::getCity, sysUser.getCity())
                .andEquals(SqfwShopsBaseInfo::getDistrict, sysUser.getDistrict())
                .andEquals(SqfwShopsBaseInfo::getCounty, sysUser.getCounty());
        if (!CommonConstants.DIRECTLY_UNDER.equals(sysUser.getCommunity())) {
            sqlGroup.andEquals(SqfwShopsBaseInfo::getCommunity, sysUser.getCommunity());
        }
        List<SqfwShopsBaseInfo> list = this.query(cnd);
        shopsComponent.initUpdateAddress(list);
        List<SqfwShopsBaseInfo> result = new ArrayList<>();
        List<SqfwShopsBaseInfo> result1 = new ArrayList<>(); // 不是已停业的其他数据
        List<SqfwShopsBaseInfo> result2 = new ArrayList<>(); // 已停业的数据
        // 已停业的放在最后面
        for (SqfwShopsBaseInfo shopsBaseInfo : list) {
            updateInitData(shopsBaseInfo);
            if (SqfwFormSummaryFieldEnum.CHECK_STATUS_CLOSED.getCode().equals(shopsBaseInfo.getShopStatus())) {
                result2.add(shopsBaseInfo);
                continue;
            }
            result1.add(shopsBaseInfo);
        }
        result.addAll(result1);
        result.addAll(result2);
        Pagination pagination = new Pagination();
        pagination.setList(result);
        pagination.setTotalCount(result.size());
        return Result.data(pagination);
    }

    private void updateInitData(SqfwShopsBaseInfo shopsBaseInfo) {
        // 初始化一些导入的数据
        boolean isUpdate = false;
        String detailAddress = ShopsComponent.buildDetailAddress(shopsBaseInfo);
        if (StringUtils.isNotEmpty(detailAddress)) {
            isUpdate = true;
        }
        if (StringUtils.isEmpty(shopsBaseInfo.getShopStatus())) {
            shopsBaseInfo.setShopStatus("1");
            isUpdate = true;
        }
        if (isUpdate) {
            this.updateIgnoreNull(shopsBaseInfo);
        }
    }

    @Override
    public void changeCloseDown(String shopId) {
        Sqfw_formSummary_new sqfwFormSummary = formsummaryNewService.fetch(shopId);
        if (sqfwFormSummary != null) {
            sqfwFormSummary.setCheckStatus(SqfwFormSummaryFieldEnum.CHECK_STATUS_CLOSED.getCode());
            sqfwFormSummary.setUpdateDate(new Date())
                    .setUpdateBy(SecurityUtil.getUserId())
                    .setUpdateUserName(SecurityUtil.getUserUsername());
            formsummaryNewService.update(sqfwFormSummary);
        }
        // 修改商铺表状态
        SqfwShopsBaseInfo shopsBaseInfo = this.fetch(shopId);
        if (shopsBaseInfo != null) {
            shopsBaseInfo.setShopStatus(SqfwFormSummaryFieldEnum.CHECK_STATUS_CLOSED.getCode());
            shopsBaseInfo.setDeleted(true);
            formsummaryNewService.updateIgnoreNull(shopsBaseInfo);
        }
    }

    @Override
    public Sqfw_formSummary_new getShopDetailById(String shopId) {
        // 查询商铺基本信息
        SqfwShopsBaseInfo shopsBaseInfo = this.fetch(shopId);
        if (shopsBaseInfo == null) {
            Sqfw_formSummary_new summaryNew = new Sqfw_formSummary_new();
            summaryNew.setShopsBaseInfo(shopsBaseInfo);
            return summaryNew;
        }
        // 2 查询历史填写信息
        Cnd cnd = Cnd.NEW();
        SqlExpressionGroup sqlGroup = cnd.where();
        sqlGroup.andEquals(Sqfw_formSummary_new::getShopsBaseInfoId, shopId)
                .andEquals(Sqfw_formSummary_new::getProvince, shopsBaseInfo.getProvince())
                .andEquals(Sqfw_formSummary_new::getCity, shopsBaseInfo.getCity())
                .andEquals(Sqfw_formSummary_new::getDistrict, shopsBaseInfo.getDistrict())
                .andEquals(Sqfw_formSummary_new::getCounty, shopsBaseInfo.getCounty())
                .andEquals(Sqfw_formSummary_new::getCommunity, shopsBaseInfo.getCommunity())
        ;
        cnd.getOrderBy().desc(Sqfw_formSummary_new::getFillDate);
        List<Sqfw_formSummary_new> list = formsummaryNewService.query(cnd);
        if (list.size() == 0) {
            Sqfw_formSummary_new summaryNew = new Sqfw_formSummary_new();
            summaryNew.setShopsBaseInfoId(shopsBaseInfo.getId());
            summaryNew.setShopsBaseInfo(shopsBaseInfo);
            return summaryNew;
        }
        // 3 多条数据 解析数据
        Sqfw_formSummary_new summaryNew = list.get(0);
        summaryNew.setShopsBaseInfo(shopsBaseInfo);
        return summaryNew;
    }

    @Override
    public Result<?> getPlaceInfoList(PlaceParamDTO placeParamDTO) {

        StringBuilder sqlStrCount = buildPlaceSql(placeParamDTO, null, null);
        int count = formsummaryNewService.count(Sqls.create(" select count(*) from ( " + sqlStrCount + " ) c "));

        Integer pageNo = placeParamDTO.getPageNo();
        Integer pageSize = placeParamDTO.getPageSize();
        //分页查询不传默认查询前20条
        if (null == pageNo) {
            pageNo = 1;
        }
        if (null == pageSize) {
            pageSize = 20;
        }
        if (pageNo >= 1) {
            pageNo = (pageNo - 1) * pageSize;
        }
        StringBuilder sqlStr = buildPlaceSql(placeParamDTO, pageNo, pageSize);
        List<NutMap> nutMapList = this.listMap(Sqls.create(sqlStr.toString()));
        for (NutMap map : nutMapList) {
            List<PeopleTagDTO> peopleTagDTO = null;
            try {
                peopleTagDTO = JSONUtil.toList(map.get("tag").toString(), PeopleTagDTO.class);
            } catch (Exception e) {
            }
            map.put("tags", JSONUtil.toJsonStr(peopleTagDTO));
        }
        Pagination pagination = new Pagination(placeParamDTO.getPageNo(), placeParamDTO.getPageSize(), count);
        pagination.setList(JSONUtil.toList(JSONUtil.toJsonStr(nutMapList), SqfwShopBaseInfoDTO.class));
        ShopsComponent.clearLabel(pagination.getList(SqfwShopsBaseInfo.class), placeParamDTO.getSource());
        return Result.data(pagination);
    }

    @Override
    public SqfwShopsBaseInfo saveShopInfo(SqfwShopsBaseInfo info) {
        if (StringUtils.isEmpty(info.getServiceType())) {
            throw new AppException("serviceType不能为空！");
        }
        SupportBusinessTypeEnum supportBusinessTypeEnum = SupportBusinessTypeEnum.getCodeByName(info.getServiceType());;
        Sys_user sysUser = userService.getUserById(SecurityUtil.getUserId());
        // 赋值五级基本信息
        info.setProvince(StringUtils.isBlank(info.getProvince()) ? sysUser.getProvince() : info.getProvince())
                .setCity(StringUtils.isBlank(info.getCity()) ? sysUser.getCity() : info.getCity())
                .setDistrict(StringUtils.isBlank(info.getDistrict()) ? sysUser.getDistrict() : info.getDistrict())
                .setCounty(StringUtils.isBlank(info.getCounty()) ? sysUser.getCounty() : info.getCounty())
                .setCommunity(StringUtils.isBlank(info.getCommunity()) ? sysUser.getCommunity() : info.getCommunity());
        // 商铺详细地址 唯一
        ShopsComponent.buildDetailAddress(info);
        // 检查商铺是否存在
        SqfwShopsBaseInfo shopsBaseInfo = this.findByDetailAddress(info.getDetailAddress());
        if (ObjectUtils.isNotEmpty(shopsBaseInfo)) {
            throw new ServiceException("该区域下已有相同场所名及地址的数据！");
        }
        info.setServiceType(ObjectUtils.isNotEmpty(supportBusinessTypeEnum) ? supportBusinessTypeEnum.getCode() : info.getServiceType());
        info.setProvince(sysUser.getProvince());
        info.setCity(sysUser.getCity());
        info.setDistrict(sysUser.getDistrict());
        info.setCounty(sysUser.getCounty());
        info.setCommunity(sysUser.getCommunity());
        info.setShopStatus(StringUtils.isNotEmpty(info.getShopStatus()) ? info.getShopStatus() : SqfwFormSummaryFieldEnum.check_status_init.getCode());
        info.setInspectionSituation(StringUtils.isNotEmpty(info.getInspectionSituation()) ? info.getInspectionSituation() : SqfwFormSummaryFieldEnum.CHECK_SITUATION_NO.getCode());
        info.setDeleted(SqfwFormSummaryFieldEnum.CHECK_STATUS_CLOSED.getCode().equals(info.getShopStatus()));
        info.setUpdateId(sysUser.getId());
        info.setCreateId(sysUser.getId());
        info.setCreateDate(LocalDateTime.now());
        info.setUpdateDate(LocalDateTime.now());
        return this.insert(info);
    }

    @Override
    public SqfwShopsBaseInfo updateShopInfo(SqfwShopsBaseInfo info) {
        SupportBusinessTypeEnum supportBusinessTypeEnum = SupportBusinessTypeEnum.getCodeByName(info.getServiceType());
       /* if (ObjectUtils.isEmpty(supportBusinessTypeEnum)) {
            throw new ServiceException(String.format("【%s】该业务类型入口未放开权限！", info.getServiceType()));
        }*/
        SqfwShopsBaseInfo shopsBaseInfo = this.fetch(info.getId());
        info.setProvince(shopsBaseInfo.getProvince())
                .setCity(shopsBaseInfo.getCity())
                .setCommunity(shopsBaseInfo.getCommunity())
                .setCounty(shopsBaseInfo.getCounty())
                .setDistrict(shopsBaseInfo.getDistrict());
        if (ObjectUtils.isEmpty(shopsBaseInfo)) {
            throw new ServiceException("商铺信息不存在！");
        }
        Sys_user sysUser = userService.getUserById(SecurityUtil.getUserId());
        Cnd cnd = Cnd.NEW();
        cnd.and(SqfwShopsBaseInfo::getPlaceName, "=", info.getPlaceName());
        // 地址组装查询
        if (StringUtils.isNotEmpty(info.getActualAddress())) {
            cnd.and(SqfwShopsBaseInfo::getActualAddress, "=", info.getActualAddress());
        } else {
            cnd.and(SqfwShopsBaseInfo::getAddress, "=", info.getAddress());
        }
        cnd.and(SqfwShopsBaseInfo::getProvince, "=", StringUtils.isBlank(info.getProvince()) ? sysUser.getProvince() : info.getProvince());
        cnd.and(SqfwShopsBaseInfo::getCity, "=", StringUtils.isBlank(info.getCity()) ? sysUser.getCity() : info.getCity());
        cnd.and(SqfwShopsBaseInfo::getDistrict, "=", StringUtils.isBlank(info.getDistrict()) ? sysUser.getDistrict() : info.getDistrict());
        cnd.and(SqfwShopsBaseInfo::getCounty, "=", StringUtils.isBlank(info.getCounty()) ? sysUser.getCounty() : info.getCounty());
        cnd.and(SqfwShopsBaseInfo::getCommunity, "=", StringUtils.isBlank(info.getCommunity()) ? sysUser.getCommunity() : info.getCommunity());

        SqfwShopsBaseInfo getShopsBaseInfo = this.fetch(cnd);
        if (ObjectUtils.isNotEmpty(getShopsBaseInfo)) {
            if (!getShopsBaseInfo.getId().equals(info.getId())) {
                throw new ServiceException("该区域下已有相同场所名、地址的数据！");
            }
        }
        info.setUpdateId(SecurityUtil.getUserId());
        info.setUpdateDate(LocalDateTime.now());
        info.setCreateId(shopsBaseInfo.getCreateId());
        info.setCreateDate(shopsBaseInfo.getCreateDate());
        if (ObjectUtils.isNotEmpty(supportBusinessTypeEnum)) {
            //打标签
            if (StringUtils.isBlank(shopsBaseInfo.getServiceType())) {
                info.setServiceType(supportBusinessTypeEnum.getCode());
            } else if (!shopsBaseInfo.getServiceType().contains(supportBusinessTypeEnum.getCode())) {
                info.setServiceType(shopsBaseInfo.getServiceType() + "," + supportBusinessTypeEnum.getCode());
            } else {
                info.setServiceType(shopsBaseInfo.getServiceType());
            }
        } else {
            info.setServiceType(shopsBaseInfo.getServiceType());
        }
        // 构建标签
        shopsComponent.buildLable(info, shopsBaseInfo);
        //兼容老数据 停业
        info.setDeleted(SqfwFormSummaryFieldEnum.CHECK_STATUS_CLOSED.getCode().equals(info.getShopStatus()));
        // 组装详细地址唯一
        ShopsComponent.buildDetailAddress(info);
        this.updateIgnoreNull(info);
        return info;
    }

    @Override
    public SqfwShopsBaseInfo getShopInfoDetail(String id) {
        SqfwShopsBaseInfo shopsBaseInfo = this.fetch(id);
        if (ObjectUtils.isEmpty(shopsBaseInfo)) {
            throw new ServiceException("场所信息不存在！");
        }
        shopsBaseInfo.setServiceType(null);
        return shopsBaseInfo;
    }

    @Override
    public SqfwShopsBaseInfo findByDetailAddress(String detailAddress) {
        return this.fetch(Cnd.where(SqfwShopsBaseInfo::getDetailAddress, "=", detailAddress));
    }

    private StringBuilder buildPlaceSql(PlaceParamDTO placeParamDTO, Integer pageNo, Integer pageSize) {
        StringBuilder sqlStr = new StringBuilder();
        String content = placeParamDTO.getContent();
        String type = placeParamDTO.getServiceType();
        String placeType = placeParamDTO.getPlaceType();
        String source = placeParamDTO.getSource();
        String checkStatus = placeParamDTO.getCheckStatus();
        String status = placeParamDTO.getStatus();
        Sys_user sysUser = userService.getUserById(SecurityUtil.getUserId());
        String typeCode = null;
        String code = null;//字典code
        if (StringUtils.isNotBlank(type)) {
            //当传入服务类型的时候 默认查不出数据,只有从枚举里面找到对应的服务后才能查看，当服务类型为空时默认查询当前账号五级下的所有数据
            typeCode = "无";
            SupportBusinessTypeEnum supportBusinessTypeEnum = SupportBusinessTypeEnum.getCodeByName(type);
            if (ObjectUtils.isNotEmpty(supportBusinessTypeEnum)) {
                typeCode = supportBusinessTypeEnum.getCode();
            }
            //字典code配置方式，如安全排查金阳街道按天：day_safety_check_金阳街道，按月：month_safety_check_金阳街道,按年：year_safety_check_金阳街道
            //这里查询时只组装后半部分，因为不确定按什么方式计算超时，所以下面查询字典模糊查询拿到时间单位
            code = String.format("%s_%s",  typeCode, sysUser.getCounty());
        } else {
            if (StringUtils.isBlank(content)) {
                throw new ServiceException("业务类型跟搜索内容不能同时为空 ！");
            }
            //source有的情况下按此字段取值，没有则默认查询安全排查
            SupportBusinessTypeEnum supportBusinessTypeEnum = SupportBusinessTypeEnum.getCodeByName(StringUtils.isNotBlank(source) ? source : SupportBusinessTypeEnum.SAFE_CHECK.getName());
            if (ObjectUtils.isNotEmpty(supportBusinessTypeEnum)) {
                //查到对应的业务后拼接字典code
                //字典code配置方式，如安全排查金阳街道按天：day_safety_check_金阳街道，按月：month_safety_check_金阳街道,按年：year_safety_check_金阳街道
                //这里查询时只组装后半部分，因为不确定按什么方式计算超时，所以下面查询字典模糊查询拿到时间单位
                code = String.format("%s_%s",supportBusinessTypeEnum.getCode(), sysUser.getCounty());
            }
        }

        sqlStr.append(" select * from ( select a.*,COALESCE(c.fill_date,a.create_date) as fill_date");
        //先从字典查询，如果有则使用字典的数据，字典配置计算超时周期
        //计算超时时间开始
        Sys_dict sysDict = null;
        if (StringUtils.isNotBlank(code)) {
            //查询字典数据
            sysDict = this.dictService.getDictByLikeCode(code);
        }
        boolean flag =false;
        if (SupportBusinessTypeEnum.PUBLIC_SECURITY.getName().equals(source)) {
            //配置了字典
            if (ObjectUtils.isNotEmpty(sysDict)) {
                String unitCode=sysDict.getCode().split("_")[0];
                //目前只支持年、月、日三个单位配置
                flag = CheckTimeoutEnum.verification(unitCode);
            }
            if(flag){
                //目前只按天/月/年配置
                sqlStr.append(" ,case when ( now() - INTERVAL '").append(sysDict.getName()).append(" ").append(sysDict.getCode().split("_")[0]).append(" ')>=coalesce(c.fill_date,a.create_date) then 3 else 1 end as timeout_status ");
            }else{
                //未配置字典的情况下 默认按本周
                sqlStr.append(" ,case  when TO_CHAR(now() , 'IW')!=TO_CHAR(COALESCE(c.fill_date,a.create_date) , 'IW') then 3 else 1 end as timeout_status ");
            }
            sqlStr.append(" ,c.param_data ->>'placeType' as place_type ");

        } else {
            //对应街道没有的情况下查询通用的（按天计算）
            if (ObjectUtils.isEmpty(sysDict)) {
                sysDict = this.dictService.getByCode(CommonConstants.DAY_SAFETY_CHECK_COMMON);
            }
            //配置了字典
            if (ObjectUtils.isNotEmpty(sysDict)) {
                String unitCode=sysDict.getCode().split("_")[0];
                //目前只支持年、月、日三个单位配置
                flag = CheckTimeoutEnum.verification(unitCode);
            }
            if(flag){
                //目前只按天/月/年配置
                sqlStr.append(" ,case when ( now() - INTERVAL '").append(sysDict.getName()).append(" ").append(sysDict.getCode().split("_")[0]).append(" ')>=coalesce(c.fill_date,a.create_date) then 3 else 1 end as timeout_status ");
            }else{
                //未配置字典的情况下 默认30天
                sqlStr.append(" ,case when ( now() - INTERVAL '30 day')>=coalesce(c.fill_date,a.create_date) then 3 else 1 end as timeout_status ");
            }
            sqlStr.append(" ,case when ( a.type !=' ' and a.type  is not null)  then  a.type   else c.placeType end  as place_type ");

        }
        //计算超时时间结束
        sqlStr.append("  from (");
        //店铺sql
        StringBuilder sqlShop = new StringBuilder();
        sqlShop.append(" select ");
        sqlShop.append(" id,");
        sqlShop.append(" coalesce(type,'') as type,");
        sqlShop.append(" coalesce(place_name,'') as place_name,");
        sqlShop.append(" coalesce(operator,'') as operator,");
        sqlShop.append(" coalesce(address,'') as address,");
        sqlShop.append(" coalesce(actual_address,'') as actual_address,");
        sqlShop.append(" coalesce(operator_telephone,'') as operator_telephone, ");
        sqlShop.append(" create_date,");
        sqlShop.append(" coalesce(social_credit_code,'') as social_credit_code,");
        sqlShop.append(" coalesce(business_license_name,'') as business_license_name,");
        sqlShop.append(" coalesce(business_license_image,'') as business_license_image,");
        sqlShop.append(" coalesce(detail_address,'') as detail_address,");
        sqlShop.append(" coalesce(place_area,'') as place_area,");
        sqlShop.append(" coalesce(main_business_scope,'') as main_business_scope,");
        sqlShop.append(" coalesce(inspection_situation,'') as inspection_situation,");
        sqlShop.append(" coalesce(service_type,'') as service_type,");
        sqlShop.append(" coalesce(longitude,'') as longitude,");
        sqlShop.append(" coalesce(latitude,'') as latitude,");
        sqlShop.append(" coalesce(business_type,'') as business_type,");
        sqlShop.append(" business_param as tag,");
        sqlShop.append(" coalesce(plot_code,'') as plot_code,");
        sqlShop.append(" CASE WHEN shop_status='3' THEN 3 ELSE 1 END as shop_status  ");
        sqlShop.append(" from shequ.sqfw_shops_base_info ssbi where ");
        sqlShop.append("  province ='").append(sysUser.getProvince()).append("'");
        sqlShop.append(" and city ='").append(sysUser.getCity()).append("'");
        sqlShop.append(" and district ='").append(sysUser.getDistrict()).append("'");
        sqlShop.append(" and county ='").append(sysUser.getCounty()).append("'");
        sqlShop.append(" and community ='").append(sysUser.getCommunity()).append("'");
        //不上查下，涉及数据回显问题
        /*if (!CommonConstants.DIRECTLY_UNDER.equals(sysUser.getCity())) {
            sqlShop.append(" and city ='").append(sysUser.getCity()).append("'");
        }
        if (!CommonConstants.DIRECTLY_UNDER.equals(sysUser.getDistrict())) {
            sqlShop.append(" and district ='").append(sysUser.getDistrict()).append("'");
        }
        if (!CommonConstants.DIRECTLY_UNDER.equals(sysUser.getCounty())) {
            sqlShop.append(" and county ='").append(sysUser.getCounty()).append("'");
        }
        if (!CommonConstants.DIRECTLY_UNDER.equals(sysUser.getCommunity())) {
            sqlShop.append(" and community ='").append(sysUser.getCommunity()).append("'");
        }*/
        if (StringUtils.isNotBlank(typeCode)) {
            sqlShop.append(" and service_type like '%").append(typeCode).append("%'");
        }
        if (StringUtils.isNotBlank(content)) {
            sqlShop.append(" and (place_name like '%").append(content).append("%' or address like '%").append(content).append("%')");
        }
        if (SupportBusinessTypeEnum.SAFE_CHECK.getName().equals(source)) {
            if (StringUtils.isNotBlank(placeType)) {
                sqlShop.append(" and type in ").append(StrUtil.formatIds(Arrays.asList(placeType.split(","))));
            }
        }

        //店铺状态 3-停业，1、2、空未停业
        if (StringUtils.isNotBlank(status)) {
            if (SqfwFormSummaryFieldEnum.CHECK_STATUS_CLOSED.getCode().equals(status)) {
                sqlShop.append(" and shop_status = '3'");
            } else {
                sqlShop.append(" and ( shop_status <> '3' or shop_status is null) ");
            }
        }
        if (null != pageNo) {
            //如果是搜索就不要内层分页
            if (StringUtils.isBlank(content)) {
                //不是搜索的情况下有两种情况，1是筛选的有状态，则根据条件排序取值，如果是没状态的则正序排号后根据时间排取值
                if (StringUtils.isNotBlank(status)) {
                    if (SqfwFormSummaryFieldEnum.CHECK_STATUS_CLOSED.getCode().equals(status)) {
                        sqlShop.append(" order by  shop_status desc ");
                    } else {
                        sqlShop.append(" order by  shop_status asc ");
                    }
                } else {
                    //sqlShop.append(" order by  shop_status asc ");
                    sqlShop.append(" order by update_date desc ");
                }
                //数据量大的情况下排序会很慢，因此如果是从筛选查询则这里只从商铺查询1000出来筛选
                sqlShop.append(" LIMIT 1000 OFFSET 0");
            }
        } else {
            //没有筛选值直接返回商铺总数，统计时需要
            if (StringUtils.isBlank(content) && StringUtils.isBlank(checkStatus) && StringUtils.isBlank(placeType)) {
                return sqlShop;
            }
        }
        // 商铺 sql  结束
        sqlStr.append(sqlShop);
        sqlStr.append(" ) a   ");
        //关联记录表，根据巡查时间取超时状态，社会治安还需要取场所类型
        sqlStr.append(" left join ( ").append(buildFormSummarySql(sysUser, type, placeType, content, source)).append(" )c on c.shops_base_info_id=a.id   ");
        sqlStr.append(" ) e where 1=1   ");
        //超时状态status，0-全部,1-已超时，3-未超时
        if (SqfwFormSummaryFieldEnum.CHECK_SITUATION_NO.getCode().equals(checkStatus)) {
            sqlStr.append(" and e.timeout_status ='1'");
        }
        if (SqfwFormSummaryFieldEnum.CHECK_SITUATION_YES.getCode().equals(checkStatus)) {
            sqlStr.append(" and e.timeout_status ='3' ");
        }
        if (StringUtils.isNotBlank(placeType)) {
            sqlStr.append(" and e.place_type is not null");
            sqlStr.append(" and e.place_type in ").append(StrUtil.formatIds(Arrays.asList(placeType.split(","))));
        }
        if (null != pageNo) {
            sqlStr.append(" order by e.fill_date desc,e.id");

            //sqlStr.append(" order by e.fill_date desc, e.shop_status asc, e.timeout_status desc");
            sqlStr.append(" LIMIT ").append(pageSize).append(" OFFSET ").append(pageNo);
        }
        return sqlStr;

    }


    private String buildFormSummarySql(Sys_user sysUser, String type, String placeType, String content, String source) {
        StringBuilder sqlStr = new StringBuilder();
        if (sysUser.getUnitName().contains(StreetEnum.WEI_QING_MEN_STREET.getName()) &&
                SqfwFormSummaryConstants.safety_check.equals(source)) {
            sqlStr.append(" select *,b.param_data ->>'attributesOfInspectionUnits' as placeType from (   ");
        } else {
            sqlStr.append(" select *,b.param_data ->>'type' as placeType from (   ");
        }
        sqlStr.append(" select *,row_number() over(partition by shops_base_info_id order by fill_date desc) as num from shequ.sqfw_form_summary_new sfsn where  ");
        sqlStr.append(" province ='").append(sysUser.getProvince()).append("'");
        sqlStr.append(" and city ='").append(sysUser.getCity()).append("'");
        sqlStr.append(" and district ='").append(sysUser.getDistrict()).append("'");
        sqlStr.append(" and county ='").append(sysUser.getCounty()).append("'");
        sqlStr.append(" and community ='").append(sysUser.getCommunity()).append("'");

        if (StringUtils.isNotBlank(type)) {
            sqlStr.append(" and type = '").append(type).append("'");
        }
        /*if (!CommonConstants.DIRECTLY_UNDER.equals(sysUser.getCity())) {
            sqlStr.append(" and city ='").append(sysUser.getCity()).append("'");
        }
        if (!CommonConstants.DIRECTLY_UNDER.equals(sysUser.getDistrict())) {
            sqlStr.append(" and district ='").append(sysUser.getDistrict()).append("'");
        }
        if (!CommonConstants.DIRECTLY_UNDER.equals(sysUser.getCounty())) {
            sqlStr.append(" and county ='").append(sysUser.getCounty()).append("'");
        }
        //sqlStr.append(" and county ='").append(sysUser.getCounty()).append("'");
        if (!CommonConstants.DIRECTLY_UNDER.equals(sysUser.getCommunity())) {
            sqlStr.append(" and community ='").append(sysUser.getCommunity()).append("'");
        }*/
        if (StringUtils.isNotBlank(content)) {
            sqlStr.append(" and (param_data ->>'placeName' like '%").append(content).append("%' or param_data ->>'address' like '%").append(content).append("%')");
        }
        sqlStr.append(" ) b where b.num=1   ");
        if (StringUtils.isNotBlank(placeType)) {
            if (SupportBusinessTypeEnum.PUBLIC_SECURITY.getName().equals(source)) {
                sqlStr.append(" and b.param_data ->>'placeType' in ").append(StrUtil.formatIds(Arrays.asList(placeType.split(","))));
            } else if (sysUser.getUnitName().contains(StreetEnum.WEI_QING_MEN_STREET.getName()) &&
                    SqfwFormSummaryConstants.safety_check.equals(source)) {
                sqlStr.append(" and b.param_data ->>'attributesOfInspectionUnits' in ").append(StrUtil.formatIds(Arrays.asList(placeType.split(","))));
            } else {
                sqlStr.append(" and b.param_data ->>'type' in ").append(StrUtil.formatIds(Arrays.asList(placeType.split(","))));
            }
        }


        return sqlStr.toString();
    }

}
